<?php
/**
* Classe permettant de manipuler les bases de données
* Classe utilisant le design patern singleton 
* Accés depuis l'exterieur via la methode statique database::getInstance()
* 
*/
class Database {

	protected static $connection = null;

	/**
	* @desc : Constructeur, initialise la connection
	* @return void
	*/
	private function __construct()
	{
		try {
			// On instancie PDO et on se connecte a la base de données
			self::$connection = new PDO('mysql:host=localhost;dbname=m2l', 'root', '',  array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
		} catch(PDOException $e) {
			echo "Erreur de connection à la base de données : <b>".$e->getMessage()."</b>";
		}
	}

	/**
	* @desc : Methode statique d'accès à la database, retourne une instance de la connection si elle existe, en créer une si aucune existante
	* @return instance de database
	*/
	public static function getInstance() {
		if(self::$connection === null) {
			return new self();
		}
		return self::$connection;
	}

	/* ###############################################################################################################################
	*
	*	*********************************          DEBUT DES METHODES DES ADHERENTS ***************************
	*
	* ############################################################################################################################### */

	public function infoAdherent($idAdherent) {

		$query = self::$connection->prepare("SELECT nom_adherent, prenom_adherent, adresse_adherent, ville_adherent, cp_adherent, certificat_adherent, sexe_adherent, date_inscription_adherent 
											 FROM adherent 
											 WHERE numero_licence = :numero_licence");
		
		$query->bindValue(":numero_licence", $idAdherent, PDO::PARAM_INT);

		$query->execute();

		if($query->fetchColumn() >= 1) {
			return $query->fetch(PDO::FETCH_OBJ);
		}

		return false;
	}

		public function listAdherent (){

			$query = self::$connection->prepare("SELECT nom_adherent, prenom_adherent, adresse_adherent, ville_adherent, cp_adherent, certificat_adherent, sexe_adherent, date_inscription_adherent 
											 	 FROM adherent
											 	 WHERE ");

			$query->execute();
			
			return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function checkLogin($login) {

		$query = self::$connection->prepare("SELECT count(*) FROM connexion WHERE mail_connexion = :mail_connexion");
		$query->bindValue(":mail_connexion", $login, PDO::PARAM_STR);

		$query->execute();

		if($query->fetchColumn() == 1) {	
			return true;
		}

		return false;

	}

	public function checkPassword($login, $password) {

		$query = self::$connection->prepare("SELECT count(*) FROM connexion WHERE password_connexion = :password_connexion AND mail_connexion = :mail_connexion");
		$query->bindValue(":password_connexion", $password, PDO::PARAM_STR);
		$query->bindValue(":mail_connexion", $login, PDO::PARAM_STR);

		$query->execute();

		if($query->fetchColumn() == 1) {	
			return true;
		}

		return false;

	}

	public function getIdAdherent($login){

		$query = self::$connection->prepare("SELECT numero_licence
										 	 FROM adherent, connexion 
											 WHERE connexion.numero_connexion = adherent.connexion_numero_connexion 
											 AND mail_connexion = :login");
		$query->bindValue(":login", $login, PDO::PARAM_STR);
		$query->execute();
		$result = $query->fetch(PDO::FETCH_OBJ);

		// On débute une nouvelle session
		session_start($result->numero_licence);

		// On stock dans la session l'id de l'utilisateur
		$_SESSION["id"] = $result;

		return $result;
		
	}

	public function listReservationFromAdherant($idAdherant) {
		$query = self::$connection->prepare("SELECT * FROM reservation WHERE numero_licence = :numero_licence AND date >= now()");
		$query->bindValue(":numero_licence", $idAdherant, PDO::PARAM_INT);

		$query->execute();

		if($query->fetchColumn() > 0) {
			return $query->fetchAll(PDO::FETCH_OBJ);
		}
		else {
			return false;
		}
	}

	public function getIdAssoc($idAdherant) {
		$query = self::$connection->prepare("SELECT numero_association
											FROM association, association_has_adherent, adherent
											WHERE numero_licence = :idAdherant
											AND numero_licence = adherent_numero_licence
											AND association_numero_association = numero_association");
		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getNomAdherent ($idAdherant){

		$query = self::$connection->prepare("SELECT nom_adherent 
											 FROM connexion, adherent 
											 WHERE connexion.numero_connexion = adherent.connexion_numero_connexion
											 AND numero_licence = :idAdherant");
		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function isAdmin($idAdherant) {
		$query = self::$connection->prepare("SELECT is_admin 
											FROM connexion, adherent 
											WHERE adherent.connexion_numero_connexion = connexion.numero_connexion 
											AND adherent.numero_licence = :idAdherant");
		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);

		$query->execute();

		if($query->fetch(PDO::FETCH_OBJ)->is_admin == 1) {
			return true;
		} else {
			return false;
		}
	}

	/* ###############################################################################################################################
	*
	*	*********************************          FIN DES METHODES DES ADHERENTS ***************************
	*
	* ############################################################################################################################### */

	/* ###############################################################################################################################
	*
	*	*********************************    DEBUT DES METHODES DU MATERIEL ***************************
	*
	* ############################################################################################################################### */

	public function addMateriel($description, $reference, $tailleLot, $idEtat, $idMaterielSport) {
		
		$query = self::$connection->prepare("INSERT INTO materiel(description, reference, etat_reservation, taille_lot, etat_materiel_id_etat, materiel_sport_id_materiel_sport) 
											VALUES(:description, :reference, :etatReservation, :tailleLot, :idEtat, :idMaterielSport)
											");
		$query->bindValue(":description", $description, PDO::PARAM_STR);
		$query->bindValue(":reference", $reference, PDO::PARAM_STR);
		$query->bindValue(":tailleLot", $tailleLot, PDO::PARAM_INT);
		$query->bindValue(":etatReservation", false, PDO::PARAM_BOOL);
		$query->bindValue(":idEtat", $idEtat, PDO::PARAM_INT);
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		} 

		return false;
	}

	public function updateMateriel($idMateriel, $idEtat, $idMaterielSport, $description, $tailleLot, $reference) {

		$currentTailleLot = $this->getCurrentTailleLot($idMateriel);

		if($tailleLot < $currentTailleLot) {
			$this->updateStockMaterielSport($idMaterielSport, $tailleLot, "delete");
		}

		if($tailleLot > $currentTailleLot) {
			$this->updateStockMaterielSport($idMaterielSport, $tailleLot,"add");
		}
		
		$query = self::$connection->prepare("UPDATE materiel SET description = :description, reference = :reference, taille_lot = :tailleLot, etat_materiel_id_etat = :idEtat, materiel_sport_id_materiel_sport = :idMaterielSport WHERE idMateriel = :idMateriel");
		$query->bindValue(":description", $description, PDO::PARAM_STR);
		$query->bindValue(":reference", $reference, PDO::PARAM_STR);
		$query->bindValue(":tailleLot", $tailleLot, PDO::PARAM_INT);
		$query->bindValue(":idEtat", $idEtat, PDO::PARAM_INT);
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		} 

		return false;
	}

	public function updateStockMaterielSport($idMaterielSport, $tailleLot, $action="add") {
		$query = self::$connection->prepare("UPDATE materiel_sport SET stock = :stock WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		switch($action) {
			case "add":
				$query->bindValue(":stock", $this->getStock($idMaterielSport)->stock+$tailleLot, PDO::PARAM_INT);
				break;
			case "delete":
				$query->bindValue(":stock", $this->getStock($idMaterielSport)->stock-$tailleLot, PDO::PARAM_INT);
				break;
		}
		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}
	
	public function getListe($idMaterielSport, $idAssociation) {
		$query = self::$connection->prepare("SELECT id_materiel, description FROM materiel, materiel_sport WHERE materiel_sport_id_materiel_sport = :idMaterielSport AND association_numero_association = :idAssociation GROUP BY id_materiel");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);

		$query->execute();
		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function getAllEtat() {
		$query = self::$connection->prepare("SELECT id_etat, type_etat FROM etat_materiel");
		$query->execute();
		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function getIdMaterielSport($idMateriel) {
		$query = self::$connection->prepare("SELECT materiel_sport_id_materiel_sport FROM materiel WHERE id_materiel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getCurrentTailleLot($idMateriel) {
		$query = self::$connection->prepare("SELECT taille_lot FROM materiel WHERE id_materiel = :idMateriel");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getStock($idMaterielSport) {

		$query = self::$connection->prepare("SELECT stock FROM materiel_sport WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function addMaterielSport($designation, $idAssociation) {
		$query = self::$connection->prepare("INSERT INTO materiel_sport(designation_materiel_sport, association_numero_association)  VALUES(:designation, :idAssociation)");
		$query->bindValue(":designation", $designation, PDO::PARAM_STR);
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}
	
	public function updateMaterielSport($idMaterielSport, $designation) {
		$query = self::$connection->prepare("UPDATE materiel_sport SET designation_materiel_sport = :designation WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue("designation", $designation, PDO::PARAM_STR);
		$query->bindValue("idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}

	public function deleteMaterielSport($idMaterielSport) {
		$query = self::$connection->prepare("DELETE FROM materiel_sport WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() > 0) {
			return true;
		}
		return false;
	}

	public function deleteMaterielFromMaterielSport($idMaterielSport) { 
		$query = self::$connection->prepare("DELETE FROM materiel WHERE materiel_sport_id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		if($query->execute()) {
			return true;
		}
		return false;
	}

	public function getInfoMaterielSport($idMaterielSport) {
		$query = self::$connection->prepare("SELECT id_materiel_sport, designation_materiel_sport FROM materiel_sport WHERE id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);


		$query->execute();

		return $query->fetch(PDO::FETCH_OBJ);
	}

	public function getAllMaterielSportByAssoc($idAssociation) {
		$query = self::$connection->prepare("SELECT id_materiel_sport, designation_materiel_sport FROM materiel_sport WHERE association_numero_association = :idAssociation");
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);
		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}

		public function getAllMateriel($idMaterielSport) {
		$query = self::$connection->prepare("SELECT id_materiel, description FROM materiel WHERE materiel_sport_id_materiel_sport = :idMaterielSport");
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);
		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}




	public function getMaterielStockByDay($idMaterielSport, $idMateriel, $idAssociation, $date) {
		$query = self::$connection->prepare('SELECT taille_lot
											FROM materiel, materiel_sport, association
											WHERE materiel_sport_id_materiel_sport = :idMaterielSport
											AND numero_association = :idAssociation
											AND id_materiel = :idMateriel
											GROUP BY taille_lot;
											');
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);
		$query->bindValue(":idMaterielSport", $idMaterielSport, PDO::PARAM_INT);

		$query->execute();

		$tailleLot = $query->fetch(PDO::FETCH_OBJ);

		$query = self::$connection->prepare('SELECT SUM(materiel_reservation.quantite) as qte
											FROM reservation, materiel_reservation
											WHERE reservation.id_reservation = materiel_reservation.reservation_id_reservation
											AND materiel_reservation.materiel_id_materiel = :idMateriel
											AND date = :date');

		$query->bindValue(":date", $date, PDO::PARAM_STR);
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);
	
		$query->execute();

		$quantite = $query->fetch(PDO::FETCH_ASSOC);

		$totalStock = $tailleLot->taille_lot-$quantite["qte"];

 		echo $totalStock;
	}

	/* ###############################################################################################################################
	*
	*	*********************************          FIN DES METHODES DU MATERIEL ***************************
	*
	* ############################################################################################################################### */

	/* ###############################################################################################################################
	*
	*	*********************************          DEBUT DES METHODES DES RESERVATIONS ***************************
	*
	* ############################################################################################################################### */

	public function addReservation($date, $idAdherent, $idMateriel, $quantite) {


		$query = self::$connection->prepare("INSERT INTO reservation(date, adherent_numero_licence) VALUES(:date, :adherent_numero_licence)");
		$query->bindValue(":date", $date, PDO::PARAM_STR);
		$query->bindValue(":adherent_numero_licence", $idAdherent, PDO::PARAM_INT);

		$query->execute();

		$id = self::$connection->lastInsertId();

		$query = self::$connection->prepare("INSERT INTO materiel_reservation(materiel_id_materiel, reservation_id_reservation, quantite) VALUES(:idMateriel, :idReservation, :qte)");
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);
		$query->bindValue(":idReservation", $id, PDO::PARAM_INT);
		$query->bindValue(":qte", $quantite, PDO::PARAM_INT);

		$query->execute();


	
		if($query->rowCount() == 1 ){
			return true;
		}
		return false;
	}

	// permet de mettre à jour une reservation deja existante.

	public function updateReservation($date, $idAdherent, $idMateriel, $quantite, $idReservation) {

		
		$query = self::$connection->prepare("UPDATE reservation AS r, materiel_reservation AS mr SET r.date = :date AND mr.quantite = :qte AND mr.materiel_id_materiel = :idMateriel WHERE r.id_reservation = :idReservation");
		$query->bindValue(":date", $date);
		$query->bindValue(":qte", $quantite, PDO::PARAM_INT);
		$query->bindValue(":idMateriel", $idMateriel, PDO::PARAM_INT);
		$query->bindValue(":idReservation", $idReservation, PDO::PARAM_INT);
		$query->execute();


	
		if($query->rowCount() == 1 ){
			return true;
		}
		return false;
	}

		//suppression d'une réservation.
	public function deleteReservation($idReservation) {

		$this->modifieDisponibiliteMateriel($idReservation, $etat=true);

		$query = self::$connection->prepare("DELETE FROM reservation WHERE id_reservation = :id_reservation");
		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);

		$query->execute();

		if($query->rowCount() == 1) {
			return true;
		}

		return false;

	}

		//modifie l'état d'utilisation du matériel.
	public function modifieDispoibiliteMateriel($idReservation,$etat){


		$query = sefl::$connection->prepare("UPDATE etat_reservation SET materiel, materiel_reservation
											WHERE materiel.id_materiel = materiel_reservation.materiel_id_materiel
											AND id_reservation = :id_reservation ") ;
		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);
		$query->bindValue(":etat_reservation", $etat, PDO::PARAM_BOOL);

		$query ->execute();

		if($query->rowCount() > 0){
			return true;
		}
		return false;
	}

		// récupère les info d'une réservation.

	public function infoReservation($idReservation) {

		$query = self::$connection->prepare("SELECT date,description,reference,quantite, nom_adherent, prenom_adherent, designation_materiel_sport, id_materiel_sport, id_materiel
											 FROM materiel_reservation,materiel, reservation, adherent, materiel_sport
											 WHERE reservation.id_reservation = materiel_reservation.reservation_id_reservation 
											 AND materiel_reservation.materiel_id_materiel = materiel.id_materiel 
											 AND adherent.numero_licence = reservation.adherent_numero_licence
											 AND materiel.materiel_sport_id_materiel_sport = materiel_sport.id_materiel_sport
											 AND id_reservation = :id_reservation");

		$query->bindValue(":id_reservation", $idReservation, PDO::PARAM_INT);

		$query->execute();

		//if($query->fetchColumn() > 0){
			return $query->fetch(PDO::FETCH_OBJ);
		//}

	}

		// récupère la listes des clubs/associations d'un adhérent.
	public function recuperationAssociationAdherent($idAdherant){

		$query = self::$connection->prepare("SELECT numero_association FROM association_has_adherent
											 WHERE adherent_numero_license= :idAdherant");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}

		// permet de récupérer la liste du matériel disponible pour un adhérent selon ses clubs/associations.
	public function listeMaterielPourAdherent($idAdherant) {

		$listeAssociation = $this->recuperationAssociationAdherent();

		$query = self::$connection->prepare("SELECT materiel_sport.id_materiel_sport FROM association, materiel_sport 
											 WHERE materiel_sport.association_numero_association = association.numero_association
											 AND association.numero_association = :listeAssociation
											 AND materiel_sport.stock > 0
											 AND materiel.etat_reservation = false");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->bindValue(":listeAssociation", $listeAssociation->adherant_numero_license, PDO::PARAM_INT);
		$query->execute();

		if($query->fetchColumn() > 0){
			return $query->fetchAll(PDO::FETCH_OBJ);
		}
		return false;
	}

	public static function checkMyNextReservation($idAdherant, $year, $month) { 

		$dateDeb = new DateTime($year."-".$month."-01");
		$dateFin = new DateTime($year."-".$month."-01");
		$dateFin->add(new DateInterval('P6M'));

		$query = self::$connection->prepare("SELECT id_reservation, date, quantite, materiel_id_materiel, description, reference, designation_materiel_sport, nom_adherent, prenom_adherent
											FROM reservation AS r, materiel_reservation AS mr, materiel AS m, materiel_sport AS ms, adherent AS a
											WHERE  r.id_reservation = mr.reservation_id_reservation
											AND r.adherent_numero_licence = :idAdherant
											AND mr.materiel_id_materiel = m.id_materiel
											AND m.materiel_sport_id_materiel_sport = ms.id_materiel_sport
											AND r.adherent_numero_licence = a.numero_licence
											AND date BETWEEN :dateDeb AND :dateFin
											");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->bindValue(":dateDeb", $dateDeb->format("Y-m-d"));
		$query->bindValue(":dateFin", $dateFin->format("Y-m-d"));

		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function getMyNextReservation($idAdherant, $year, $month, $day) { 

		$dateDeb = new DateTime($year."-".$month."-".$day);
		$dateFin = new DateTime($year."-".$month."-".$day);
		$dateFin->add(new DateInterval('P6M'));

		$query = self::$connection->prepare("SELECT id_reservation, date, quantite, materiel_id_materiel, description, reference, designation_materiel_sport, nom_adherent, prenom_adherent
											FROM reservation AS r, materiel_reservation AS mr, materiel AS m, materiel_sport AS ms, adherent AS a
											WHERE  r.id_reservation = mr.reservation_id_reservation
											AND r.adherent_numero_licence = :idAdherant
											AND mr.materiel_id_materiel = m.id_materiel
											AND m.materiel_sport_id_materiel_sport = ms.id_materiel_sport
											AND r.adherent_numero_licence = a.numero_licence
											AND date BETWEEN :dateDeb AND :dateFin
											");

		$query->bindValue(":idAdherant", $idAdherant, PDO::PARAM_INT);
		$query->bindValue(":dateDeb", $dateDeb->format("Y-m-d"));
		$query->bindValue(":dateFin", $dateFin->format("Y-m-d"));

		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}


	public static function getAllReservationForAssoc($idAssociation, $year, $month) {

		$dateDeb = new DateTime($year."-".$month."-01");
		$dateFin = new DateTime($year."-".$month."-01");
		$dateFin->add(new DateInterval('P6M'));

		$query = self::$connection->prepare("SELECT id_reservation, date, quantite, materiel_id_materiel, description, reference, designation_materiel_sport, nom_adherent, prenom_adherent
											FROM reservation AS r, materiel_reservation AS mr, materiel AS m, materiel_sport AS ms, adherent AS ad, association AS a 
											WHERE  r.id_reservation = mr.reservation_id_reservation
											AND mr.materiel_id_materiel = m.id_materiel
											AND m.materiel_sport_id_materiel_sport = ms.id_materiel_sport
											AND r.adherent_numero_licence = ad.numero_licence
											AND a.numero_association = :idAssociation
											AND date BETWEEN :dateDeb AND :dateFin
											ORDER BY date
											");

		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);
		$query->bindValue(":dateDeb", $dateDeb->format("Y-m-d"));
		$query->bindValue(":dateFin", $dateFin->format("Y-m-d"));

		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}

	public function findReservation($idAssociation, $date, $term) {

		$query = self::$connection->prepare("SELECT nom_adherent, prenom_adherent, id_reservation, date
								FROM adherent AS ad, association_has_adherent AS aha, association AS ass, reservation AS r
								WHERE aha.association_numero_association = ass.numero_association
								AND aha.adherent_numero_licence = ad.numero_licence
								AND r.adherent_numero_licence = ad.numero_licence
								AND date >= :date
								AND ass.numero_association = :idAssociation
								AND ad.nom_adherent LIKE :term 
								");

		$query->bindValue(":date", $date);
		$query->bindValue(":idAssociation", $idAssociation, PDO::PARAM_INT);
		$query->bindValue(":term", $term."%");

		$query->execute();

		return $query->fetchAll(PDO::FETCH_OBJ);
	}
	/* ###############################################################################################################################
	*
	*	*********************************          FIN DES METHODES DES RESERVATIONS ***************************
	*
	* ############################################################################################################################### */

}
	
?>